package com.mainbo.batch.service.impl;

import com.mainbo.batch.bean.PtClass;
import com.mainbo.batch.service.PtClassService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * @Author xww
 * @Description //TODO
 * @Date 2019/8/6   9:38
 **/
@Service
public class PtClassServiceImpl implements PtClassService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void BatChinSertClass() {
        int size = namedParameterJdbcTemplate.getJdbcOperations().queryForObject("select count(1) from pt_class where school_year='2018' and enable=1", Integer.class);
        String querysql = "select * from pt_class where school_year='2018'and enable=1";
        Integer Page = 1000;
        List<PtClass> list = namedParameterJdbcTemplate.query(querysql, new BeanPropertyRowMapper<>(PtClass.class));
        String prefix = "INSERT INTO pt_class VALUES ";
        StringBuffer suffix = new StringBuffer();
        Long begin = new Date().getTime();

        Date date = new Date();   //这里的时util包下的
        SimpleDateFormat temp = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  //这是24时
        String Date = temp.format(date);
        try {
            Connection connection = jdbcTemplate.getDataSource().getConnection();
            connection.setAutoCommit(false);
            PreparedStatement preparedStatement = connection.prepareStatement(prefix);
            int num = size / Page + 1;
            System.out.println("共有 ： " + size + "条，！" + " 分为 ：" + num + "批");

            int numData = 0;
            for (int j = 1; j <= num; j++) {

                for (int i = numData; i < numData + Page; i++) {
                    if (i == list.size()) {
                        break;
                    }
                    suffix.append("('").append(UUID.randomUUID().toString().replace("-", "")).append("','");
                    suffix.append(list.get(i).getClassType() == null ? 0 : list.get(i).getSubjectType()).append("','");
                    suffix.append(list.get(i).getName()).append("','").append(list.get(i).getOrgId()).append("','").append(list.get(i).getOrgName()).append("','").append(list.get(i).getPeriod()).append("','").append(list.get(i).getPhaseId()== null ? 140 : list.get(i).getPhaseId()).append("','");
                    suffix.append(list.get(i).getSubjectType() == null ? 0 : list.get(i).getSubjectType()).append("','");
                    suffix.append(list.get(i).getGradeId() == null ? '0' : list.get(i).getGradeId()).append("','").append(list.get(i).getTeacherId() == null ? "" : list.get(i).getTeacherId()).append("','");
                    suffix.append(list.get(i).getTeacherName() == null ? "" : list.get(i).getTeacherName()).append("','").append(list.get(i).getSort() == null ? '0' : list.get(i).getSort()).append("','");
                    suffix.append(list.get(i).getEnable()).append("','").append(list.get(i).getCrtId()).append("','").append(Date).append("','").append("admin").append("','").append(Date).append("','").append(list.get(i).getSchoolYear()+1).append("','");
                    suffix.append(list.get(i).getStatus() + "','" +0).append("','").append(list.get(i).getFromClassId() == null ? '0' : list.get(i).getFromClassId()).append("'),");
                }

                String sql = prefix + suffix.substring(0, suffix.length() - 1);

                preparedStatement.addBatch(sql);

                preparedStatement.executeBatch();

                connection.commit();

                suffix = new StringBuffer();
                numData += Page;
            }

            preparedStatement.close();
            connection.close();


            Long end = new Date().getTime();

            System.out.println("除去读取文件时间，只算插入数据库的时间为 : " + (end - begin)/1000 + " s");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println(size);
    }
}
